/** 
Copyright (c) 2010, Sheikh Abdul Wahid Ahmed
Details @ http://extremecodeworld.codeplex.com/license
**/

using System;
using EXtremecode.Utils;
using System.Collections;
using System.Data;
using EXtremecode.DataAccessLayer;
using EXtremecode.DataAccessLayer.Configurations;
using System.Text;
using EXtremecode;
using System.Web;
using EXtremecode.Common;
using System.Data.Common;
using System.Collections.Generic;

namespace EXtremecode.DataAccessLayer
{
	public delegate IDbCommand CommandCreatorHandler(string table,string view,DataAccessor da,bool forAdapter);
	/// <summary>
	/// Summary description for DataAccessor.
	/// </summary>
	public class AccessorType
	{
		public const string SQL_SERVER="SQLServer";
	};
	
	public abstract class  DataAccessor
	{
		protected string m_sAccessorName;
		protected string m_sConnectionURL;
		protected DataSet m_dataBaseImage=null;
		protected Hashtable htCommandsList=new Hashtable();
		//it should not be static because more than one database can contain object of the
		//same name but of diffrent schema.

		
		#region SYSTEM QUERIES
		internal abstract string QUERY_Table{get;}
		internal abstract string QUERY_View {get;}
		internal abstract string QUERY_TableColumn{get;}
       	#endregion

		#region SYSTEM COLUMNS
		const string COLUMN_TableName="Table_Name";
		const string COLUMN_ColummnName="ColumnName";
		const string COLUMN_Name="Name";
		const string COLUMN_TypeCode="TypeCode";
		#endregion

        public abstract string DBNow { get; }
        protected abstract DataTable GetRelationInfo();
		internal protected DataAccessor(string sAccessorName,string sConnectionURL,bool validationRequired)
		{
			m_sAccessorName=sAccessorName;
			m_sConnectionURL=sConnectionURL;

            if (validationRequired)
            {

                IDbConnection conn = CreateConnection();
                try
                {
                    conn.Open();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw Logging.Logger.Write("Accessor Name:" + sAccessorName, ex);
                }
            }
			
		}

        internal protected DataAccessor(string sAccessorName, string sConnectionURL): this(sAccessorName,sConnectionURL,true)
        {}
		
		public string Create_SelectQueryString(string strViewName)
		{
            return string.Format("Select {0}.* from {0} tblMain", GetEnclosedDbObject(strViewName));
		}
		private static DataRow[] GetSeletedFKTables(DataTable dtRelations,string strPKTableName)
		{
			//It's possible that more than one relations b/w two tables but only one should be used.
			Hashtable htFKTableDataRows=new Hashtable();
			ArrayList alFKTables=new ArrayList();
			foreach(DataRow dr in dtRelations.Select(string.Format("PK_TABLE_NAME='{0}'",strPKTableName)))
			{
				string strFKTableName=dr["FK_TABLE_NAME"].ToString();
				string strRelationName=dr["FK_NAME"].ToString();

				if(!htFKTableDataRows.Contains(strFKTableName))
				{
					DataRow[] drsFKTable=dtRelations.Select(string.Format("PK_TABLE_NAME='{0}' and FK_NAME='{1}'",strPKTableName,strRelationName));
					htFKTableDataRows.Add(strFKTableName,drsFKTable);
					alFKTables.AddRange((ICollection)drsFKTable);
				}
			}
			DataRow[] drs=new DataRow[0];
			if(alFKTables.Count>0)
				drs=(DataRow[])alFKTables.ToArray(alFKTables[0].GetType());
			return drs;

		}
		protected void BuildRelations(DataSet ds,DataTable dtRelationsInfo,string accessorName)
		{
			//for Logging Purpose
			string strLoggingParentTable="";	
			string strLoggingChildTable="";	
			string strParentDbTableName="";	
			string strChildDataTableName="";
			try
			{
				foreach(DataTable dtPK in ds.Tables)
				{
					
					strLoggingParentTable=dtPK.TableName;//for logging
					strParentDbTableName=dtPK.TableName;
					
					
					if(dtPK.ExtendedProperties.Contains("DuplicateName"))
					{
						strParentDbTableName=dtPK.TableName.Substring(dtPK.TableName.IndexOf('-')+1);
						string strDbName=dtPK.TableName.Substring(0,dtPK.TableName.IndexOf('-'));
						if(strDbName != accessorName)continue;
					}
					

					DataRow[] drsSelectedFKTables=GetSeletedFKTables(dtRelationsInfo,strParentDbTableName);
					foreach(DataRow dr in drsSelectedFKTables)
					{
						string sChildTable=dr["FK_TABLE_NAME"].ToString();
						string sFKName=dr["FK_NAME"].ToString();

						strLoggingChildTable=sChildTable;//for logging
						strChildDataTableName=string.Format("{0}-{1}",accessorName,sChildTable);
						if(!ds.Tables.Contains(strChildDataTableName))strChildDataTableName=sChildTable;
						string strRelationName=StringProcessor.RelationName(dtPK.TableName,strChildDataTableName);
						if(ds.Relations.Contains(strRelationName))continue;
						DataRow[] drsSelectedFKTableFields=dtRelationsInfo.Select(string.Format("PK_TABLE_NAME='{0}' and FK_TABLE_NAME='{1}' and FK_NAME='{2}'",strParentDbTableName,sChildTable,sFKName));
						DataColumn[] dcsParent=new DataColumn[drsSelectedFKTableFields.Length];
						DataColumn[] dcsChild=new DataColumn[drsSelectedFKTableFields.Length];
						for(int i=0; i<drsSelectedFKTableFields.Length; i++)
						{
							string sParentCol=drsSelectedFKTableFields[i]["PK_COLUMN_NAME"].ToString();
							string sChildCol=drsSelectedFKTableFields[i]["FK_COLUMN_NAME"].ToString();
							dcsParent[i]=dtPK.Columns[sParentCol];
							dcsChild[i]=ds.Tables[strChildDataTableName].Columns[sChildCol];

						}
						ds.Relations.Add(strRelationName,dcsParent,dcsChild);
						

					}

					
				}
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(
					string.Format("AccessorName=[{2}] ParentTable=[{0}],ChildTable=[{1}]",
					strLoggingParentTable,
					strLoggingChildTable,accessorName),ex);
			}
			finally
			{
				Logging.Logger.Write(string.Format("AccessorName=[{2}] ParentTable=[{0}],ChildTable=[{1}]",
					strLoggingParentTable,
					strLoggingChildTable,accessorName));
			}
			
		}
		
			
		
		
		public abstract string GetParameterName(string paramName);
		public virtual string GetEnclosedDbObject(string dbObjectName)
		{
			return string.Format("[{0}]",dbObjectName);
		}
		public abstract IDataParameter GetParameter(string name,string type);
        public virtual IEnumerable<IDbCommand> GetInternalCommands(IDbCommand mainCommand)
        {

            yield return mainCommand;
            yield break;
        }
		protected abstract IDbConnection  CreateConnection();
		public abstract IDbCommand CreateCommand();
        internal abstract IDbDataAdapter CreateDataAdapter();
        internal virtual string AdjustUDType4BinaryField(int intTypeCode, string strUDType)
        {
            return strUDType;
        }
        internal abstract string GeneratePagingQuery(string selectQuery, string sortedBy, bool isSortingTypeAscending, int pageNumber, int pageSize, long? totalRecords);




        internal IDbConnection CreateNewConnection()
        {
            return CreateConnection();
        }
		public  long GetTokenValue(string strTokenId,bool transactionBased,bool updateTokenValue)
		{
			
			long intTokenValue=1;
			string strTableName=Provider.TokenTableName;
			string strIdField=Provider.TokenIdField;
			string strValueField=Provider.TokenValueField;
			string strSelectQuery=string.Format("Select * from {0} where {1}='{2}'",GetEnclosedDbObject(strTableName),
				GetEnclosedDbObject(strIdField),strTokenId);
			bool isconnectionOpen=false;
			IDbCommand  cmd=CreateCommand();
			cmd.CommandText=strSelectQuery;
			if(transactionBased)
			{
				cmd.Connection=this.TransactionConnection;
				cmd.Transaction=this.Transaction; 
			}
			else
			{
				cmd.Connection=CreateConnection();
				cmd.Connection.Open();isconnectionOpen=true;
			}
			try
			{
				IDataReader rdr=cmd.ExecuteReader();
				if(rdr.Read())
				{
					intTokenValue=long.Parse(rdr[strValueField].ToString());
					rdr.Close();
					if(updateTokenValue)
					{
						cmd.CommandText=string.Format("Update {0} set {1}={2} where {3}='{4}'",
							GetEnclosedDbObject(strTableName),
							GetEnclosedDbObject(strValueField),
							intTokenValue+1,
							GetEnclosedDbObject(strIdField),
							strTokenId);
						cmd.ExecuteNonQuery();
					}

				}
				else
				{
					rdr.Close();
					if(updateTokenValue)
					{
						cmd.CommandText=string.Format("Insert into {0} ({1},{2}) values ('{3}',{4})",
							GetEnclosedDbObject(strTableName),
							GetEnclosedDbObject(strIdField),
							GetEnclosedDbObject(strValueField),
							strTokenId,
							intTokenValue+1);

						cmd.ExecuteNonQuery();
					}
				}
				return intTokenValue;
			}
			catch(Exception ex)
			{
				DataAccessorCollection.Instance.Rollback(); 
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				if(!transactionBased && isconnectionOpen)cmd.Connection.Close();
				//Logging.Logger.Write();
			}
			
		}



		#region Commands n Adapter Creation
		private void SetParameterSource(IDataParameter param,string sourceColumn,DataRowVersion sourceVersion)
		{
            param.SourceVersion=sourceVersion; 
			param.SourceColumn=sourceColumn; 
		}
		public DAAdapter Create_Adapter(string strViewName,string strTableName,FieldInfoCollection fields,CommandCreatorHandler selectCmdCreator,CommandCreatorHandler updateCmdCreator,CommandCreatorHandler insertCmdCreator,CommandCreatorHandler deleteCmdCreator)
		{

			IDbDataAdapter adapter=CreateDataAdapter();

			//Normal Adapter
			IDbConnection conn=CreateConnection();
			adapter.SelectCommand=(selectCmdCreator==null )?
				Create_SelectIDbCommand(strViewName,fields):selectCmdCreator(strTableName,strViewName,this,true);
			adapter.UpdateCommand=(updateCmdCreator==null)?
				Create_UpdateIDbCommand(strTableName,strViewName,fields,true):updateCmdCreator(strTableName,strViewName,this,true);;
			adapter.InsertCommand=(insertCmdCreator==null)?
				Create_InsertIDbCommand(strTableName,strViewName,fields,true):insertCmdCreator(strTableName,strViewName,this,true);;
			adapter.DeleteCommand=(deleteCmdCreator==null)?
				Create_DeleteIDbCommand(strTableName,fields,true):deleteCmdCreator(strTableName,strViewName,this,true);;
			
			adapter.SelectCommand.Connection=conn;
			adapter.UpdateCommand.Connection=conn;
			adapter.InsertCommand.Connection=conn;
			adapter.DeleteCommand.Connection=conn;


			//Transaction Adapter
			IDbDataAdapter adapterTransaction=CreateDataAdapter();
			adapterTransaction.SelectCommand=(selectCmdCreator==null )?
				Create_SelectIDbCommand(strViewName,fields):selectCmdCreator(strTableName,strViewName,this,true);
			adapterTransaction.UpdateCommand=(updateCmdCreator==null)?
                Create_UpdateIDbCommand(strTableName,strViewName, fields, true) : updateCmdCreator(strTableName, strViewName, this, true); ;
			adapterTransaction.InsertCommand=(insertCmdCreator==null)?
				Create_InsertIDbCommand(strTableName,strViewName,fields,true):insertCmdCreator(strTableName,strViewName,this,true);;
			adapterTransaction.DeleteCommand=(deleteCmdCreator==null)?
				Create_DeleteIDbCommand(strTableName,fields,true):deleteCmdCreator(strTableName,strViewName,this,true);;
			
			adapterTransaction.SelectCommand.Connection=TransactionConnection;
			adapterTransaction.UpdateCommand.Connection=TransactionConnection;
			adapterTransaction.InsertCommand.Connection=TransactionConnection;
			adapterTransaction.DeleteCommand.Connection=TransactionConnection;

			return new DAAdapter(adapter,this,adapterTransaction,fields);
		}


		public DACommand Create_UpdateDACommand(string strTableName,string strViewName,FieldInfoCollection fields,CommandCreatorHandler creator)
		{
			IDbCommand cmd=(creator==null)? Create_UpdateIDbCommand(strTableName,strViewName,fields,false): creator(strTableName,"",this,false);
			IDbCommand cmdTransaction=(creator==null)? Create_UpdateIDbCommand(strTableName,strViewName,fields,false): creator(strTableName,"",this,false);
			cmdTransaction.Connection=TransactionConnection;
			cmd.Connection=CreateConnection();
			return new DACommand(cmd,this,cmdTransaction,fields,CommandAction.Update);
		}
		public  DACommand Create_SelectDACommand(string strViewName,FieldInfoCollection fieldsWhereClause,CommandCreatorHandler creator)
		{
			IDbCommand cmd=(creator==null)? Create_SelectIDbCommand(strViewName,fieldsWhereClause): creator("",strViewName,this,false);
			IDbCommand cmdTransaction=(creator==null)? Create_SelectIDbCommand(strViewName,fieldsWhereClause): creator("",strViewName,this,false);
			cmdTransaction.Connection=TransactionConnection;
			cmd.Connection=CreateConnection();
			return new DACommand(cmd,this,cmdTransaction,fieldsWhereClause,CommandAction.Select);
		}
		public  DACommand Create_DeleteDACommand(string strTableName,FieldInfoCollection fieldsWhereClause,CommandCreatorHandler creator)
		{
			IDbCommand cmd=(creator==null)? Create_DeleteIDbCommand(strTableName,fieldsWhereClause,false): creator(strTableName,"",this,false); 
			IDbCommand cmdTransaction=(creator==null)? Create_DeleteIDbCommand(strTableName,fieldsWhereClause,false): creator(strTableName,"",this,false);
			cmdTransaction.Connection=TransactionConnection;

			cmd.Connection=CreateConnection();
			return new DACommand(cmd,this,cmdTransaction,fieldsWhereClause,CommandAction.Delete);
		}
		public  DACommand Create_InsertDACommand(string strTableName,string strViewName,FieldInfoCollection fields,CommandCreatorHandler creator)
		{
			IDbCommand cmd=(creator==null)? Create_InsertIDbCommand(strTableName, strViewName,fields,false): creator(strTableName,strViewName,this,false); 
			IDbCommand cmdTransaction=(creator==null)? Create_InsertIDbCommand(strTableName, strViewName,fields,false): creator(strTableName,strViewName,this,false);
			cmdTransaction.Connection=TransactionConnection;

			cmd.Connection=CreateConnection();
			return new DACommand(cmd,this,cmdTransaction,fields,CommandAction.Insert);
		}

		
		private  IDbCommand Create_UpdateIDbCommand(string strTableName,string strViewName,FieldInfoCollection fields,bool forAdapter)
		{
			try
			{
				IDbCommand cmd=CreateCommand() ;
				
				//try to get command from the ht. if found than simply clone it otherwise u have
				//to created form the scratch
				string strCommandKey= string.Format("{0}_Update_{1}",strTableName.ToLower(),forAdapter);
				if(htCommandsList.Contains(strCommandKey))
				{
					Utils.DataAccessorUtil.MakeCloneOfCommand(
						htCommandsList[strCommandKey] as IDbCommand,cmd); 

					return cmd;
				}

				
                StringBuilder sbQueryColumns=new StringBuilder(string.Format("Update {0} Set ",
					GetEnclosedDbObject(strTableName)
					));

				
                
                if(!forAdapter)
				{
					//when the command is being made not for the adapter. 
					//it is used to hold the list of colums which are decided at run time
					//means at the time of execution of command.
					//but parameters will be created here.
					sbQueryColumns.Append(" {0} ");
				}

				
                
                StringBuilder sbQueryWhereClause=new StringBuilder();
                StringBuilder sbQueryExtraWhereClause = new StringBuilder();
				string strQoma="",strAnd="";
                string strAndForExtra = "";
                string strKeyParam = "";
				
				foreach(object obj in fields.Keys)
				{
					FieldInfo field=fields[obj] as FieldInfo;
					string parameterName=GetParameterName(StringProcessor.DBParameter(field.Name));
					
					if(field.IsNative)
					{
						//only native fields are related to the update command
						
						IDataParameter param=null;

                        if (!field.IsComputed)
                        {

                            if (forAdapter)
                            {
                                //if it is for the adpter then create string for the
                                //respective field as well.
                                //for business object we will make this string on the spot
                                sbQueryColumns.Append(strQoma + string.Format(" {0} = {1} ",
                                    GetEnclosedDbObject(field.Name),
                                    parameterName));
                                strQoma = " , ";
                            }
                            param = GetParameter(parameterName, field.Type);
                            SetParameterSource(param, field.Name, DataRowVersion.Current);
                            cmd.Parameters.Add(param);

                        }
					
						if(field.IsKey 
                            || field.Name.ToLower()==Provider.TimestampColumnName.ToLower())
						{
							//these are the fields which specify the condition.
							//if fields is key or have name which i have dedicated as time stamp column.

							DataRowVersion srcVer=DataRowVersion.Original;
                            parameterName = string.Format("{0}_{1}", parameterName, Constants.ParamCustomType.CONDITIONAL);
                            


							if(!field.IsKey)
							{
								//the field which are not key can have null value.
                                
                                //as these fields are not required to get respective updated row so 
                                //we are storing clause of these fields into other variable

                                param = GetParameter(parameterName, field.Type);
                                SetParameterSource(param, field.Name, srcVer);
                                cmd.Parameters.Add(param);
                                
                                sbQueryExtraWhereClause.Append(strAndForExtra + string.Format(" ( {0} = {1} or {1} is null ) ",
                                    GetEnclosedDbObject(field.Name), parameterName));
                                strAndForExtra = " And "; 
							}
							else
							{
                                parameterName = parameterName + ColumnSuffix.KeyParam;

                                param = GetParameter(parameterName, field.Type);
                                SetParameterSource(param, field.Name, srcVer);
                                cmd.Parameters.Insert(0,param); // keys should be on top


                                if (!field.IsComputed)
                                {
                                    sbQueryWhereClause.Append(strAnd + string.Format("({0} = {1} or ({2} is not null and {2} = {0})) ",
                                        GetEnclosedDbObject(field.Name), parameterName
                                        , GetParameterName(StringProcessor.DBParameter(field.Name))));
                                }
                                else
                                {
                                    sbQueryWhereClause.Append(strAnd + string.Format("{0} = {1}",
                                        GetEnclosedDbObject(field.Name), parameterName
                                        ));
                                }
                                strAnd = " And ";

                            }

                            
						}

					}
				 	
				}
				
                //--Making query by joining it's parts.
                cmd.CommandText = sbQueryColumns.ToString();


                string strWhereClause=sbQueryWhereClause.ToString();
                string strWhereClauseWithExtra = strWhereClause;
                string strExtraWhereClause = sbQueryExtraWhereClause.ToString();

                //appending extra where clause to  normal where clause 
                if (strExtraWhereClause != string.Empty)
                {
                    if (strWhereClause == string.Empty)
                    {
                        strWhereClauseWithExtra = strExtraWhereClause;
                    }
                    else
                    {
                        strWhereClauseWithExtra = string.Format(" {0} and {1} "
                            ,strWhereClause
                            ,strExtraWhereClause); 
                    }
                }


                
                //appending where clause to query.
                if (!string.IsNullOrEmpty(strWhereClauseWithExtra))
                {
                  cmd.CommandText +=  string.Format(" where {0} ",
                        strWhereClauseWithExtra);
                }


                //appending select query to final query, for getting updated row
                if (!string.IsNullOrEmpty(strWhereClause))
                {
                    
                    cmd.CommandText += string.Format(" ;select * from {0} where {1}",
                        strViewName,
                        strWhereClause);
                }

				//save into ht.
				IDbCommand clonedCmd=CreateCommand();
				Utils.DataAccessorUtil.MakeCloneOfCommand(cmd,clonedCmd);
				htCommandsList[strCommandKey]= clonedCmd;
				
				return cmd;
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				//Logging.Logger.Write();
			}
		}

		private  IDbCommand Create_SelectIDbCommand(string strViewName,FieldInfoCollection fieldsWhereClause)
		{
			try
			{
				
				IDbCommand  cmd=CreateCommand();
				
				//try to get command from the ht. if found than simply clone it otherwise u have
				//to created form the scratch
				string strCommandKey= string.Format("{0}_Select",strViewName.ToLower());
				if(htCommandsList.Contains(strCommandKey))
				{
					Utils.DataAccessorUtil.MakeCloneOfCommand(
						htCommandsList[strCommandKey] as IDbCommand,cmd); 

					return cmd;
				}

                string querystrat = @"Select {0}.* from {0}  
                            {1} 
                            where 1=1 ";
				
                if(!string.IsNullOrEmpty(Provider.WhenDeletedColumnName) 
                    &&  fieldsWhereClause.Contains(Provider.WhenDeletedColumnName))
				{
					//if table contains "WhenDelted" field then we need to show only records
                    // which have "WhenDelted" null
                    querystrat = @"Select {0}.* from {0}  
                                {1}
                                where " 
                            + Provider.WhenDeletedColumnName +" is null";
				}

				
				StringBuilder sbQueryColumns=new StringBuilder(string.Format(querystrat,
					GetEnclosedDbObject(strViewName)
                    , "{0}"));
				foreach(object obj in fieldsWhereClause.Keys)
				{
					IDataParameter param=null;
					FieldInfo field=fieldsWhereClause[obj] as FieldInfo;

					
					if(field.Type != Constants.UDTypes.BINARY) 
					{
						//binaray fields cant be used as where clause.
						string parameterName=GetParameterName(StringProcessor.DBParameter(field.Name));
						param=GetParameter(string.Format("{0}_{1}", parameterName, Constants.ParamCustomType.CONDITIONAL),field.Type);
						cmd.Parameters.Add(param);  
						SetParameterSource(param,field.Name,DataRowVersion.Current);
						
						
						if(field.IsRange) 
						{
							//if field type is range then create two more parameters
                            param = GetParameter(string.Format("{0}{1}_{2}", parameterName, Constants.ColumnSuffix.FROM, Constants.ParamCustomType.CONDITIONAL), field.Type);
							cmd.Parameters.Add(param);
							SetParameterSource(param,field.Name,DataRowVersion.Current);

                            param = GetParameter(string.Format("{0}{1}_{2}", parameterName, Constants.ColumnSuffix.TO, Constants.ParamCustomType.CONDITIONAL), field.Type);
							cmd.Parameters.Add(param);
							SetParameterSource(param,field.Name,DataRowVersion.Current);

						}
					}
					
				 	
				}
				cmd.CommandText=sbQueryColumns.ToString(); 
				
				//save into ht.
				IDbCommand clonedCmd=CreateCommand();
				Utils.DataAccessorUtil.MakeCloneOfCommand(cmd,clonedCmd);
				htCommandsList[strCommandKey]= clonedCmd;

				
				return cmd;
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				//Logging.Logger.Write();
			}
		}


		private  IDbCommand Create_DeleteIDbCommand(string strTableName,FieldInfoCollection fieldsWhereClause,bool forAdapter)
		{
			try
			{
				
				IDbCommand cmd=CreateCommand();

				//try to get command from the ht. if found than simply clone it otherwise u have
				//to created form the scratch
				string strCommandKey= string.Format("{0}_Delete_{1}",strTableName.ToLower(),forAdapter);
				if(htCommandsList.Contains(strCommandKey))
				{
					Utils.DataAccessorUtil.MakeCloneOfCommand(
						htCommandsList[strCommandKey] as IDbCommand,cmd); 

					return cmd;
				}

				
				string querystrat = "Delete from {0} ";
				if(!string.IsNullOrEmpty(Provider.WhenDeletedColumnName) 
                    &&  fieldsWhereClause.Contains(Provider.WhenDeletedColumnName))
				{

                    //if table contains "WhenDeleted" field then we have to just change
                    //its value to current dateTime.

                    querystrat = "Update {0} set " 
                        + Provider.WhenDeletedColumnName +  " = " 
                        + DBNow;
				}
				
				
				StringBuilder sbQueryColumns=new StringBuilder(
					string.Format(querystrat,GetEnclosedDbObject(strTableName)));
				StringBuilder sbQueryWhereClause=new StringBuilder();
				string strAnd="";
				
				foreach(object obj in fieldsWhereClause.Keys)
				{
					IDataParameter param=null;
					FieldInfo field=fieldsWhereClause[obj] as FieldInfo;
					string parameterName=GetParameterName(StringProcessor.DBParameter(field.Name));

					if(!field.IsNative)
					{
						//parameter cant be created for the field which is not native.
						continue;
					}

					if(field.Type != Constants.UDTypes.BINARY) 
					{
						//binaray fields cant be used as where clause.
						if(!forAdapter)
						{
							//if command is not created for adapter, which will be used by DataSet,
							//then we have to create two extra parameter for the field of range type.
							//for adapter we have to create parameter of row version 'original'

                            param = GetParameter(string.Format("{0}_{1}", parameterName, Constants.ParamCustomType.CONDITIONAL), field.Type);
							cmd.Parameters.Add(param);  
							SetParameterSource(param,field.Name,DataRowVersion.Current);
							if(field.IsRange) 
							{
                                param = GetParameter(string.Format("{0}{1}_{2}", parameterName, Constants.ColumnSuffix.FROM , Constants.ParamCustomType.CONDITIONAL), field.Type);
								cmd.Parameters.Add(param);
								SetParameterSource(param,field.Name,DataRowVersion.Current);

                                param = GetParameter(string.Format("{0}{1}_{2}", parameterName, Constants.ColumnSuffix.TO, Constants.ParamCustomType.CONDITIONAL), field.Type);
								cmd.Parameters.Add(param);
								SetParameterSource(param,field.Name,DataRowVersion.Current);

							}
						}
						
						else
						{

							if(field.IsKey || field.Name.ToLower()==Provider.TimestampColumnName.ToLower())
							{
								//parameters for adapter, will only be created for key and timestamp fields only.
								DataRowVersion srcVer=DataRowVersion.Original;  
								sbQueryWhereClause.Append(
									strAnd + string.Format("( {0} = {1}_{2} or {1}_{2} is null )",
                                        GetEnclosedDbObject(field.Name), parameterName, Constants.ParamCustomType.CONDITIONAL));
                                param = GetParameter(string.Format("{0}_{1}", parameterName, Constants.ParamCustomType.CONDITIONAL), field.Type);
								param.Value=DBNull.Value;//Original values should be initialized 
								SetParameterSource(param,field.Name,srcVer);
								cmd.Parameters.Add(param);
								strAnd=" and "; 
							}
						}
					
						
					}
					
				 	
				}
				string strWhereClause=sbQueryWhereClause.ToString(); 
				
				if(strWhereClause == "")
				{
						strWhereClause= " 1=1 ";
				}
				cmd.CommandText=sbQueryColumns.ToString() + " Where " + strWhereClause; 

				//save into ht.
				IDbCommand clonedCmd=CreateCommand();
				Utils.DataAccessorUtil.MakeCloneOfCommand(cmd,clonedCmd);
				htCommandsList[strCommandKey]= clonedCmd;


				return cmd;
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				//Logging.Logger.Write();
			}
		}
		private  IDbCommand Create_InsertIDbCommand(string strTableName,string strViewName,FieldInfoCollection fields,bool forAdapter)
		{
			try
			{
				
				IDbCommand cmd=CreateCommand();				
				
				//try to get command from the ht. if found than simply clone it otherwise u have
				//to create form the scratch
				string strCommandKey= string.Format("{0}_Insert_{1}",strTableName.ToLower(),forAdapter);
				if(htCommandsList.Contains(strCommandKey))
				{
					Utils.DataAccessorUtil.MakeCloneOfCommand(
						htCommandsList[strCommandKey] as IDbCommand,cmd); 

					return cmd;
				}
				
				StringBuilder sbQueryColumns=new StringBuilder();
				StringBuilder sbQueryParams=new StringBuilder();
				StringBuilder sbWhereClause=new StringBuilder();
				string strQoma="",strAnd="";
				foreach(object obj in fields.Keys)
				{
					FieldInfo field=fields[obj] as FieldInfo;
					if(field.IsNative)
					{
						//parameter cant be created for the field which is not native.

						string parameterName=GetParameterName(StringProcessor.DBParameter(field.Name));


                        if (field.IsKey)
                        {
                            //define how to get row which is just inserted. 

                            parameterName = parameterName += ColumnSuffix.KeyParam;
 
                            string strValue;
                            if (field.IsComputed)
                                strValue = "@@Identity";
                            else
                            {
                                strValue = parameterName;
                            }
                            sbWhereClause.Append(strAnd + string.Format("{0} = {1}", GetEnclosedDbObject(field.Name), strValue));
                            strAnd = " And ";
                        }


						if(!field.IsComputed)
						{
							//insert column should be only for which are not auto increamented.
							//if field is not autoincreamented then create atleast parameter for that.

							if(forAdapter)
							{
								//the command string of the command which is not for adapter,
								//will be made at run time by diciding which column's 
								//data have to be insert.
								sbQueryColumns.Append(strQoma + string.Format("{0}",GetEnclosedDbObject(field.Name)));
								sbQueryParams.Append(strQoma + parameterName);
								strQoma=" , ";
							}
							IDataParameter param=GetParameter(parameterName,field.Type);
                            SetParameterSource(param, field.Name, DataRowVersion.Current); 
                            
                            if (field.IsKey)
                            {
                                cmd.Parameters.Insert(0, param); //keys param should be on top;
                            }
                            else
                            {
                                cmd.Parameters.Add(param);
                            }
							
						}
						
					}
				}
				if(!forAdapter)
				{
					//For just defining the location in the string
					//where we will put respective list.
					sbQueryColumns.Append("{0}");
					sbQueryParams.Append("{1}");
				}
				cmd.CommandText= string.Format("Insert into {0} ({1}) values ({2})",
					GetEnclosedDbObject(strTableName),sbQueryColumns,sbQueryParams);	
				if(sbWhereClause.ToString().Trim()!="")cmd.CommandText += string.Format(" ;Select * from {0} where {1}",strViewName,sbWhereClause);
				
				//save into ht.
				IDbCommand clonedCmd=CreateCommand();
				Utils.DataAccessorUtil.MakeCloneOfCommand(cmd,clonedCmd);
				htCommandsList[strCommandKey]= clonedCmd;


				return cmd;
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				//Logging.Logger.Write();
			}

		}


		#endregion

		#region Database Image Related

		private void GenerateImage(DataSet dsCopyTo,string query,string type)
		{
			IDbConnection conn=CreateConnection();
            string strTableName = string.Empty;
			conn.Open();
			try
			{
				IDbDataAdapter adapter=CreateDataAdapter();
				adapter.SelectCommand=CreateConnection().CreateCommand();
				IDataReader rdr;
				IDbCommand cmd=CreateCommand();
				cmd.Connection=conn;
				cmd.CommandText=query;
				rdr=cmd.ExecuteReader();
				while(rdr.Read())
				{
					
					
					strTableName=rdr[COLUMN_Name].ToString();
					adapter.SelectCommand.CommandText=Create_SelectQueryString(strTableName);
                    try
                    {
                        adapter.FillSchema(dsCopyTo, SchemaType.Source);
                    }
                    catch (Exception ex)
                    {
                        Logging.Logger.Write(ex, adapter.SelectCommand.CommandText);
                        continue;
                    }
					
                    if(dsCopyTo.Tables["Table"] != null)
					{
						dsCopyTo.Tables["Table"].ExtendedProperties.Add("Type",type);
						if(dsCopyTo.Tables[strTableName] == null)
						{
							dsCopyTo.Tables["Table"].ExtendedProperties.Add("AccessorName",Name); 
							dsCopyTo.Tables["Table"].TableName=strTableName; 
						}
						else
						{
							//Curent Table
							dsCopyTo.Tables["Table"].ExtendedProperties.Add("DuplicateName",true); 
							dsCopyTo.Tables["Table"].ExtendedProperties.Add("AccessorName",Name); 
							dsCopyTo.Tables["Table"].TableName=string.Format("{0}-{1}",Name,strTableName);
							
							//Allready added table
							dsCopyTo.Tables[strTableName].ExtendedProperties.Add("DuplicateName",true); 
							dsCopyTo.Tables[strTableName].TableName=string.Format("{0}-{1}",dsCopyTo.Tables[strTableName].ExtendedProperties["AccessorName"].ToString(),strTableName);
							strTableName=string.Format("{0}-{1}",Name,strTableName);
						}
						foreach(DataColumn dc in dsCopyTo.Tables[strTableName].Columns) 
						{
							dc.AutoIncrementSeed=0;
							dc.AutoIncrementStep=-1;
						}
					}

				}
				rdr.Close();
				SetUDType(dsCopyTo);
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(
                    string.Format("Table Name:{0}",strTableName)
                    ,ex);
			}
			finally
			{
				conn.Close();
				//Logging.Logger.Write();
			}
			
			
	
		}
		public DataSet GenerateTablesImage()
		{
			DataSet ds=new DataSet(m_sAccessorName);
			GenerateTablesImage(ds);
			return ds;
		}
		public DataSet GenerateViewsImage()
		{
			DataSet ds=new DataSet(m_sAccessorName);
			GenerateViewsImage(ds);
			return ds;
		}
		public void GenerateTablesImage(DataSet dsCopyTo)
		{
			GenerateImage(dsCopyTo,QUERY_Table,"Table" );
			BuildRelations(dsCopyTo,GetRelationInfo(),Name);
		}
		public void GenerateViewsImage(DataSet dsCopyTo)
		{
			GenerateImage(dsCopyTo,QUERY_View,"View");	
		}
		public void SetUDType(DataSet ds)
		{
			IDataReader rdr;

			IDbCommand cmd=CreateCommand ();
			cmd.CommandText=QUERY_TableColumn;
			cmd.Connection=CreateConnection();
			cmd.Connection.Open();
			
			try
			{
				rdr=cmd.ExecuteReader();
				while(rdr.Read())
				{
					
					string strTableName=rdr[COLUMN_TableName].ToString();
					string strColumnName=rdr[COLUMN_ColummnName].ToString();
					int intTypeCode=int.Parse(rdr[COLUMN_TypeCode].ToString());
					
					if(!ds.Tables.Contains(strTableName) && ds.Tables.Contains(string.Format("{0}-{1}",Name,strTableName)))
					{
						strTableName=string.Format("{0}-{1}",Name,strTableName);
					}

					if(ds.Tables[strTableName] != null && ds.Tables[strTableName].Columns[strColumnName] != null)
					{
						DataColumn dc = ds.Tables[strTableName].Columns[strColumnName];
						if(!dc.ExtendedProperties.Contains(Constants.UD_TYPE))
						{
							string strUDType=FreeHash.UDType[dc.DataType.Name];
							if(strUDType==Constants.UDTypes.BINARY)
							{
								strUDType=AdjustUDType4BinaryField(intTypeCode,strUDType);
							}
							
							
							dc.ExtendedProperties.Add(Constants.UD_TYPE,strUDType);
						}
					}
			
				}
				rdr.Close();
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				cmd.Connection.Close();
				//Logging.Logger.Write();
			}
		}


	
		#endregion

		#region Transaction Related
		
		public IDbTransaction Transaction
		{			
			get
			{
				IDbTransaction tran = ThreadUtil.Instance["Xtream_Transaction"+m_sAccessorName] as IDbTransaction;

				if(tran== null)
				{
					
					try
					{
						TransactionConnection.Open();
					}
					catch{}
					tran = TransactionConnection.BeginTransaction(TransactionType);
					ThreadUtil.Instance["Xtream_Transaction"+m_sAccessorName]=tran; 
				}
				return tran;
				
			}
		}
		public IsolationLevel TransactionType
		{			
			get
			{
				object transType = ThreadUtil.Instance["Xtream_TransactionType"+m_sAccessorName];
				if(transType==null)
				{
					transType=IsolationLevel.ReadCommitted;
					ThreadUtil.Instance["Xtream_TransactionType"+m_sAccessorName]=transType;
				}
				return (IsolationLevel)transType;
			}
			set
			{
				ThreadUtil.Instance["Xtream_TransactionType"+m_sAccessorName]=value;
			}
		}

		
		/// <summary>
		/// TransactionConnection
		/// 
		/// As accessor is shared between users.
		/// But for transacttion we have to isolate transaction and its connecton.
		/// Thats why are puting them [transaction and its connection] into thead's solt.
		/// </summary>
		public IDbConnection TransactionConnection
		{			
			get
			{
				IDbConnection conn = ThreadUtil.Instance["Xtream_TransactionConn"+m_sAccessorName] as IDbConnection;
				if(conn==null)
				{
					conn=CreateConnection();
					ThreadUtil.Instance["Xtream_TransactionConn"+m_sAccessorName]=conn;
				}
				return conn;
				
			}
		}

		public bool Commit() 
		{			
			//commit the transaction and remove it from the session.
			try
			{
				IDbTransaction trans=ThreadUtil.Instance["Xtream_Transaction"+m_sAccessorName] as IDbTransaction;	
				if(trans == null) return true;
				trans.Commit();
				return true;
			}
			catch
			{
				Rollback();
				return false;
			}
			finally
			{
				try{TransactionConnection.Close();}
				catch{}
				ThreadUtil.Instance["Xtream_Transaction"+m_sAccessorName]=null;
			}
		}

		public bool Rollback()
		{
			//rollback the transaction and remove it from the session.
			try
			{
					
				IDbTransaction trans=ThreadUtil.Instance["Xtream_Transaction"+m_sAccessorName] as IDbTransaction;	
				if(trans == null) return true;
				trans.Rollback();
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				try{TransactionConnection.Close();}
				catch{}
				ThreadUtil.Instance["Xtream_Transaction"+m_sAccessorName]=null;
			}
		}

		
		public static void SetTransactionType(IsolationLevel isolationLevel)
		{
			DataAccessorCollection.Instance.SetTransactionType(isolationLevel);
		}

		public static void CommitTransaction()
		{
			
			DataAccessorCollection.Instance.Commit();
		}
		public static void RollbackTransaction()
		{
			
			DataAccessorCollection.Instance.Rollback();
		}
		public string Name
		{
			get
			{
				return m_sAccessorName; 
			}
		}

		
		#endregion

		#region Execute Query
		//Non Query
		public static int ExecuteNonQuery(string query,string connectionId,bool transactionBased)
		{
			try
			{
				DataAccessor da= DataAccessorCollection.Instance[connectionId];
				if(da == null)
				{
					throw new Exception(string.Format("[{0}] key is not defined",connectionId));
				}
				return da.doExecuteNonQuery(query,transactionBased);
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}
		}
		public static int ExecuteNonQuery(string query,string connectionId)
		{
			return ExecuteNonQuery(query,connectionId,false);	
		}
		public static int ExecuteNonQuery(string query,bool transactionBased)
		{
			return ExecuteNonQuery(query,DataAccessorCollection.DefaultAccessorName,transactionBased);	
	
		}
		public static int ExecuteNonQuery(string query)
		{
			return ExecuteNonQuery(query,DataAccessorCollection.DefaultAccessorName,false);	
		}
		
		//Fill Query
		public static DataTable ExecuteFillQuery(string query,string connectionId,bool transactionBased)
		{
			try
			{
				DataAccessor da= DataAccessorCollection.Instance[connectionId];
				if(da == null)
				{
					throw new Exception(string.Format("[{0}] key is not defined",connectionId));
				}
				return da.doExecuteFillQuery(query,transactionBased);
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}
		}

		public static void FillDataTable(string tableName,DataTable dt,string connectionId,bool transactionBased)
		{
			try
			{
				DataAccessor da= DataAccessorCollection.Instance[connectionId];
				if(da == null)
				{
					throw new Exception(string.Format("[{0}] key is not defined",connectionId));
				}
				da.doFillDataTable(string.Format("select * from {0}",
					da.GetEnclosedDbObject(tableName)),
					dt,transactionBased);
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}
		}
		
		public static DataTable ExecuteFillQuery(string query,string connectionId)
		{
			return ExecuteFillQuery(query,connectionId,false);	
		}

		public static DataTable ExecuteFillQuery(string query,bool transactionBased)
		{
			return ExecuteFillQuery(query,DataAccessorCollection.DefaultAccessorName,transactionBased);	
		}

		public static DataTable ExecuteFillQuery(string query)
		{
			return ExecuteFillQuery(query,DataAccessorCollection.DefaultAccessorName,false);	
		}

        internal IDbCommand GetPopulatedCommand(bool transactionBased)
        {
            return GetPopulatedCommand(string.Empty, transactionBased);
        }
		internal IDbCommand GetPopulatedCommand(string query,bool transactionBased)
		{
			IDbCommand cmd=CreateCommand();
			cmd.CommandText=query;
			if(transactionBased)
			{
				cmd.Transaction=Transaction;
				cmd.Connection=TransactionConnection; 
			}
			else
			{
				cmd.Connection=CreateConnection();
			}
			return cmd;
				
		}
		public int doExecuteNonQuery(string query,bool transactionBased)
		{
			try
			{
				IDbCommand cmd=GetPopulatedCommand(query,transactionBased);
				if(!transactionBased)cmd.Connection.Open();
				try
				{
					return cmd.ExecuteNonQuery();
				}
				catch
				{
					if(transactionBased)
						Rollback();
					return 0;

				}
				finally
				{
					if(!transactionBased)
						cmd.Connection.Close();
				}

			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}

		}

		public DataTable doExecuteFillQuery(string query,bool transactionBased)
		{
			try
			{
				IDbCommand cmd=GetPopulatedCommand(query,transactionBased);
				IDbDataAdapter adapter =CreateDataAdapter();
				adapter.SelectCommand=cmd;
				try
				{
					DataSet ds=new DataSet();
					adapter.Fill(ds);
					DataTable dt=null;
					if(ds.Tables.Count>0)
					{
						dt=ds.Tables[0];
						ds.Tables.Clear();
						return  dt;
					}
					return dt;
				}
                catch (Exception ex)
                {
                    throw Logging.Logger.Write(ex);
                }
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}
		
		}

		public void doFillDataTable(string query,DataTable dt,bool transactionBased)
		{
			try
			{
				IDbCommand cmd=GetPopulatedCommand(query,transactionBased);
				IDbDataAdapter adapter = CreateDataAdapter();
				adapter.SelectCommand=cmd;
				
				string strOriginalTableName=dt.TableName;
				dt.TableName="Table";
				DataSet dsOwner=dt.DataSet;
				DataSet ds;
				if(dsOwner == null) 
				{
					ds = new DataSet();
					ds.Tables.Add(dt);
				}
				else
				{
					ds=dsOwner;
				}

				try
				{
					adapter.Fill(ds);
				}
				catch(Exception ex)
				{
					throw ex;  
				}
				finally
				{
					if(dsOwner==null) ds.Tables.Clear();
					dt.TableName=strOriginalTableName;
				}
			
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}
		
		}
		#endregion

        #region Event Hendlers
        protected void Adapter_RowUpdating(object sender, RowUpdatingEventArgs e)
        {

            //timestamp column
            if (Configurations.Provider.TimestampColumnName != "")
            {
                string paramName = GetParameterName(StringProcessor.DBParameter(Configurations.Provider.TimestampColumnName));
                if (e.Command.Parameters.Contains(paramName))
                {
                    if (e.Row.Table.ExtendedProperties.Contains(Constants.KEY_IGNORE_CONCURRENCY) && (bool)(e.Row.Table.ExtendedProperties[Constants.KEY_IGNORE_CONCURRENCY]) == true)
                    {
                        //removing effect from where clause. for avoiding concurrency effect.
                        ((IDataParameter)e.Command.Parameters[paramName + "_" + Constants.ParamCustomType.CONDITIONAL]).Value = DBNull.Value;
                    }
                    //populate with current date time.
                    e.Row[Configurations.Provider.TimestampColumnName] = ((IDataParameter)e.Command.Parameters[paramName]).Value = DateTime.Now;

                }
            }
        }

        protected void Adapter_RowUpdated(object sender, RowUpdatedEventArgs e)
        {
            //concurrency
            if (e.Status == UpdateStatus.ErrorsOccurred)
            {
                if (e.Row.Table.ExtendedProperties.Contains(Constants.KEY_IGNORE_CONCURRENCY) && (bool)(e.Row.Table.ExtendedProperties[Constants.KEY_IGNORE_CONCURRENCY]) == true)
                {
                    e.Status = UpdateStatus.SkipCurrentRow;
                    e.Row.RowError = Constants.NOT_PERSISTED;
                }
            }

        }

        private string m_numOfOpendConnections = "0";
        public string NumOfOpendConnections
        {
            get { return m_numOfOpendConnections; }
            set { m_numOfOpendConnections = value; }
        }

        protected void Connection_StateChange(object sender, StateChangeEventArgs e)
        {

            lock (m_numOfOpendConnections)
            {
                if (e.CurrentState == ConnectionState.Open)
                {
                    m_numOfOpendConnections = (int.Parse(m_numOfOpendConnections) + 1).ToString();
                }
                else if (e.CurrentState == ConnectionState.Closed)
                {
                    m_numOfOpendConnections = (int.Parse(m_numOfOpendConnections) - 1).ToString();
                }

            }


            //for testing purpose.
            IDbConnection conn = sender as IDbConnection;
            Console.WriteLine("connection[{0}]={1}  (Opened Connections [{2}])", conn.Database
                , e.CurrentState
                ,m_numOfOpendConnections);

        }
        #endregion 

        public DataSet DatabaseImage
		{
			get
			{
				if(m_dataBaseImage==null)
					m_dataBaseImage=GenerateTablesImage();
				return m_dataBaseImage.Clone();
			}
		}
		public static  DataSet GetDatabaseImage()
		{
			return GetDatabaseImage(DataAccessorCollection.DefaultAccessorName);
		}
		public static  DataSet GetDatabaseImage(string connectionId)
		{
			try
			{
				DataAccessor da= DataAccessorCollection.Instance[connectionId];
				if(da == null)
				{
					throw new Exception(string.Format("[{0}] key is not defined",connectionId));
				}
				return da.DatabaseImage;
			}
			catch(Exception ex)
			{
				throw Logging.Logger.Write(ex);
			}
			finally
			{
				Logging.Logger.Write();
			}
		}
	
	
}

	
}
